const db = require('../model/database')
const sqlQuery = require('../model/query')
const dayjs = require("dayjs")

// 查询考试批次
exports.getExamBatch = async (req, res) => {
  const sql = 'SELECT * FROM test'
  const result = await sqlQuery(sql, null, db)

  if (result.length > 0) {
    res.json({
      code: 20000,
      data: result
    })
  } else {
    res.json({
      code: 20000,
      msg: '暂无数据'
    })
  }
}

// 查询考试科目及成绩
exports.getExamSubject = async (req, res) => {
  // 获取参数 分别是考试批次和班级
  const { testId, classId, type } = req.body
  // 由于可能出现新增学生的情况, 所以这里需要先查询出所有学生, 然后再查询成绩
  // 查询成绩表中该班级和该考试批次的学生id
  const sql1 = 'SELECT student_id FROM course WHERE test_id = ? AND student_id IN (SELECT id FROM student WHERE class = ?)'
  const result1 = await sqlQuery(sql1, [testId, classId], db)
  // 查询该班级的所有学生id
  const sql2 = 'SELECT id FROM student WHERE class = ?'
  const result2 = await sqlQuery(sql2, [classId], db)
  // 从result1和result2中取出值
  const result1Id = result1.map(item => item.student_id)
  const result2Id = result2.map(item => item.id)
  // 比较两个数组, 找出差集
  const diff = result2Id.filter(item => !result1Id.includes(item))
  // 将差集插入到成绩表中
  if (diff.length > 0) {
    const sql3 = 'INSERT INTO course (test_id, student_id) VALUES ?'
    const values = []
    diff.forEach(item => {
      values.push([testId, item])
    })
    await sqlQuery(sql3, [values], db)
  }

  // 由于设计三个表course test student, 所以这里使用多表查询
  // type 0: 查询成绩 1: 成绩录入
  let sql = ''
  if (type === 0) {
    sql = 'SELECT * FROM course c, test t, student s WHERE c.test_id = t.id AND c.student_id = s.id AND c.test_id = ? AND s.class = ? And c.total > 0 And s.is_delete = 0'
  } else if (type === 1) {
    sql = 'SELECT * FROM course c, test t, student s WHERE c.test_id = t.id AND c.student_id = s.id AND c.test_id = ? AND s.class = ? And s.is_delete = 0'
  }
  //let sql = 'SELECT * FROM course c, test t, student s WHERE c.test_id = t.id AND c.student_id = s.id AND c.test_id = ? AND s.class = ? And s.is_delete = 0'
  const result = await sqlQuery(sql, [testId, classId], db)
  res.json({
    code: 20000,
    data: result
  })

}

// 新增考试批次
exports.addExamBatch = async (req, res) => {
  // 获取参数 考试名称 备注
  const { title, note } = req.body
  // 插入数据库
  const sql = 'INSERT INTO test (title, note, create_time) VALUES (?, ?, ?)'
  const create_time = dayjs().format('YYYY-MM-DD HH:mm:ss')
  const result = await sqlQuery(sql, [title, note, create_time], db)

  // 新增的同时 创建相应的学生成绩记录
  // 1. 查询所有学生
  const sql1 = 'SELECT * FROM student'
  const result1 = await sqlQuery(sql1, null, db)
  // 2. 插入成绩记录
  const sql2 = 'INSERT INTO course (student_id, test_id) VALUES (?, ?)'
  for (let i = 0; i < result1.length; i++) {
    await sqlQuery(sql2, [result1[i].id, result.insertId], db)
  }

  if (result.affectedRows > 0) {
    res.json({
      code: 20000,
      msg: '新增成功'
    })
  } else {
    res.json({
      code: 20000,
      msg: '新增失败'
    })
  }

}

// 批量录入成绩
exports.addScore = async (req, res) => {
  // 获取参数
  const data = req.body
  // 更新数据库 各科成绩 总分
  const sql = 'UPDATE course SET chinese = ?, mathematics = ?, english = ?, physics = ?, chemistry = ?, biology = ?, history = ?, geography = ?, politics = ?, total = ?, average = ? WHERE student_id = ? And test_id = ?'
  for (let i = 0; i < data.length; i++) {
    const {
      chinese,
      mathematics,
      english,
      physics,
      chemistry,
      biology,
      history,
      geography,
      politics,
      total,
      average,
      student_id,
      test_id
    } = data[i]
    await sqlQuery(sql, [chinese, mathematics, english, physics, chemistry, biology, history, geography, politics, total, average, student_id, test_id], db)
  }

  res.json({
    code: 20000,
    msg: '录入成功'
  })

}

// 修改考试
exports.updateExam = async (req, res) => {
  // 获取参数
  const { id, title, note } = req.body
  // 更新数据库
  const sql = 'UPDATE test SET title = ?, note = ? WHERE id = ?'
  const result = await sqlQuery(sql, [title, note, id], db)

  if (result.affectedRows > 0) {
    res.json({
      code: 20000,
      msg: '修改成功'
    })
  } else {
    res.json({
      code: 20000,
      msg: '修改失败'
    })
  }
}

// 删除考试
exports.deleteExam = async (req, res) => {
  // 获取参数
  const { id } = req.body

  // 由于外键约束, 需要先删除course表中的数据
  const sql1 = 'DELETE FROM course WHERE test_id = ?'
  await sqlQuery(sql1, [id], db)
  // 由于外键约束, 需要先删除subject表中的数据
  const sql2 = 'DELETE FROM subject WHERE test_id = ?'
  await sqlQuery(sql2, [id], db)
  // 删除考试
  const sql = 'DELETE FROM test WHERE id = ?'
  const result = await sqlQuery(sql, [id], db)

  if (result.affectedRows > 0) {
    res.json({
      code: 20000,
      msg: '删除成功'
    })
  } else {
    res.json({
      code: 20000,
      msg: '删除失败'
    })
  }
}


// 查询考试科目及单个学生成绩-------------
exports.getExamScore = async (req, res) => {
  const testId = req.params.id
  const studentId = req.user.id
  let sql = 'SELECT * FROM course c, test t, student s WHERE c.test_id = t.id AND c.student_id = s.id AND c.test_id = ? AND c.student_id = ?'
  const result = await sqlQuery(sql, [testId, studentId], db)
  let sql1 = 'select * from subject where test_id = ?'
  const result1 = await sqlQuery(sql1, [testId], db)
  const data = [
    {
      id: 1,
      name: result1[0]?.subject,
      score: result[0]?.chinese || 0
    },
    {
      id: 2,
      name: result1[1]?.subject,
      score: result[0]?.mathematics || 0
    },
    {
      id: 3,
      name: result1[2]?.subject,
      score: result[0]?.english || 0
    },
    {
      id: 4,
      name: result1[3]?.subject,
      score: result[0]?.physics || 0
    },
    {
      id: 5,
      name: result1[4]?.subject,
      score: result[0]?.chemistry || 0
    },
    {
      id: 6,
      name: result1[5]?.subject,
      score: result[0]?.biology || 0
    },
    {
      id: 7,
      name: result1[6]?.subject,
      score: result[0]?.history || 0
    },
    {
      id: 8,
      name: result1[7]?.subject,
      score: result[0]?.geography || 0
    },
    {
      id: 9,
      name: result1[8]?.subject,
      score: result[0]?.politics || 0
    },
    {
      id: 10,
      name: '平均分',
      score: result[0]?.average || 0
    },
    {
      id: 11,
      name: '总分',
      score: result[0]?.total || 0
    }
  ]
  res.send({
    code: 20000,
    msg: '查询成功',
    data,
  })

}

// 查询学生的考试成绩
exports.getStudentScore = async (req, res) => {
  // 获取参数
  const { id } = req.params
  // 查询最新考试批次的成绩
  //const sql = 'SELECT * FROM course c, test t WHERE c.test_id = t.id AND c.student_id = ? and c.total > 0 ORDER BY t.create_time DESC LIMIT 1'
  const sql = 'SELECT * FROM course c, test t WHERE c.test_id = t.id AND c.student_id = ? ORDER BY t.create_time DESC LIMIT 1'
  let result = await sqlQuery(sql, [id], db)
  //当新增了学生但是还未给其添加成绩就进入学生档案页面
  if(result[0]===undefined){
    const sql3 = 'SELECT * FROM test ORDER BY test.create_time LIMIT 1'
    const result3 = await sqlQuery(sql3, null, db)
    result[0] = {test_id:result3[0].id}
  }
  const sql2 = 'select * from subject where test_id = ?'
  const result2 = await sqlQuery(sql2, result[0].test_id, db)

  // 生成数据
  const data = [
    {
      id: 1,
      name: result2[0].subject,
      score: result[0]?.chinese || 0
    },
    {
      id: 2,
      name: result2[1].subject,
      score: result[0]?.mathematics || 0
    },
    {
      id: 3,
      name: result2[2].subject,
      score: result[0]?.english || 0
    },
    {
      id: 4,
      name: result2[3].subject,
      score: result[0]?.physics || 0
    },
    {
      id: 5,
      name: result2[4].subject,
      score: result[0]?.chemistry || 0
    },
    {
      id: 6,
      name: result2[5].subject,
      score: result[0]?.biology || 0
    },
    {
      id: 7,
      name: result2[6].subject,
      score: result[0]?.history || 0
    },
    {
      id: 8,
      name: result2[7].subject,
      score: result[0]?.geography || 0
    },
    {
      id: 9,
      name: result2[8].subject,
      score: result[0]?.politics || 0
    },
    {
      id: 10,
      name: '平均分',
      score: result[0]?.average || 0
    },
    {
      id: 11,
      name: '总分',
      score: result[0]?.total || 0
    }
  ]
  const title = result[0]?.title || '无考试记录'
  res.json({
    code: 20000,
    data,
    title
  })
}

//查询考试科目
exports.getSubject = async (req, res, next) => {
  // 获取学院列表
  const sql = 'SELECT * FROM test'
  const testList = await sqlQuery(sql, null, db)
  // 获取专业列表
  const sql2 = 'SELECT * FROM subject'
  const subjectList = await sqlQuery(sql2, null, db)

  // 将专业添加到学院中
  testList.forEach(test => {
    test.children = []
    subjectList.forEach(subject => {
      if (test.id === subject.test_id) {
        subject.id = test.id + '-' + subject.id
        test.children.push(subject)
      }
    })
  })
  res.send({
    code: 20000,
    data: testList,
  })
}

//查询考试科目通过id
exports.getSubjectById = async (req, res) => {
  const id = req.params.id
  let sql = 'select * from subject where test_id = ?'
  const result = await sqlQuery(sql, id, db)
  res.send({
    code: 20000,
    msg: '查询成功',
    data: result
  })
}


//增加考试科目
exports.addSubject = async (req, res) => {
  const subject = req.body[0]
  const testId = req.body[1]
  const sql = `insert into subject (test_id,subject) values (?,?)`
  for (let i = 0; i < 9; i++) {
    await sqlQuery(sql, [testId, subject[i]], db)
  }
  res.send({
    code: 20000,
    msg: '添加成功'
  })
}
//修改考试科目
exports.updateSubject = async (req, res, next) => {
  const { id, subject } = req.body
  // 验证数据
  if (!id || !subject) {
    res.send({
      code: 40000,
      message: '参数错误'
    })
  }
  // 修改专业信息
  const sql = 'UPDATE subject SET subject = ? WHERE id = ?'
  const result = await sqlQuery(sql, [subject, id], db)
  if (result.affectedRows === 1) {
    res.send({
      code: 20000,
      message: '修改成功'
    })
  }
}

//-----------------

// 查询学生的成绩的班级排名
exports.getStudentRank = async (req, res) => {
  // 获取参数
  const { id } = req.params
  // 查询该学生的班级
  const sql1 = 'SELECT class FROM student WHERE id = ?'
  const result1 = await sqlQuery(sql1, [id], db)
  const class_id = result1[0].class
  // 查询考试批次,倒序
  const sql2 = 'SELECT id FROM test ORDER BY create_time DESC'
  const result2 = await sqlQuery(sql2, [], db)
  let sql3 = ''
  let result3 = []
  let testIndex = 0
  // 查询该年级的所有学生的最新考试成绩的总分
  while (result3.length === 0) {
    sql3 = `SELECT c.id as c_id, s.id, c.total FROM student s, course c WHERE s.id = c.student_id AND c.test_id = ${result2[testIndex].id} and c.total > 0 AND s.class = ?`
    result3 = await sqlQuery(sql3, [class_id], db)
    testIndex++
    if (testIndex == result2.length) {
      break;
    }
  }

  // 获取该学生的总分
  const student_total = result3.filter(item => item.id === Number(id))[0]?.total || 0
  // 整理数据
  const data = []
  for (let i = 0; i < result3.length; i++) {
    data.push(result3[i].total)
  }
  // 排序
  data.sort((a, b) => b - a)
  // 获取排名
  const rank = data.indexOf(student_total) + 1
  res.json({
    code: 20000,
    data: rank
  })
}
